﻿﻿
/* 

- datatable基本操作: https://blog.csdn.net/pengkobe123/article/details/19930055?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-0-19930055-blog-111935780.pc_relevant_multi_platform_whitelistv4&spm=1001.2101.3001.4242.1&utm_relevant_index=3

 */

using System;
using System.IO;
using System.Collections.Generic;
using OfficeOpenXml;  // dotnet add package Epplus
using System.Data;
using System.Text.RegularExpressions;

using Newtonsoft.Json;
using CsvHelper;
using CsvHelper.Configuration;
using System.Text;
using System.Globalization;


namespace AppTableUtils
{

    public class TableUtils
    {
        // public 
        public static Dictionary<K, T> test_T<K, T>(K key, T value)
        {
            Dictionary<K, T> dc = new Dictionary<K, T>();
            dc.Add(key, value);
            return dc;
        }

        public static Dictionary<string, int> test_T(string key, int value)
        {
            Dictionary<string, int> dc = new Dictionary<string, int>();
            dc.Add(key, value);
            return dc;
        }

        public static DataTable _read_csv(string f_path)
        {
            DataTable dt = new DataTable();
            using (StreamReader sr = new StreamReader(f_path, Encoding.UTF8))
            {
                string[] headers = sr.ReadLine().Split(',');
                foreach (string header in headers)
                {
                    dt.Columns.Add(header);
                }
                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dr[i] = rows[i];
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        public static void show_csv_data(string f_path)
        {
            using (FileStream fileStream = new FileStream(f_path, FileMode.Open, FileAccess.Read))
            using (StreamReader reader = new StreamReader(fileStream, Encoding.Default))
            {
                string headerLine = reader.ReadLine();
                string[] headers = headerLine.Split(',');
                BdUtils.log("headers --- ", headers);
            }

            // 读取UTF-8编码的CSV文件
            using (var reader = new StreamReader(f_path, Encoding.UTF8))
            using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture) { Encoding = Encoding.UTF8 }))
            {
                // 读取CSV文件中的所有记录
                var records = csv.GetRecords<dynamic>();
                foreach (var record in records)
                {
                    // 将记录转换为JSON字符串
                    var json = JsonConvert.SerializeObject(record);
                    Console.WriteLine(json);
                }
            }
        }

        public static DataTable read_csv(string f_path, string primary_key = null, Dictionary<string, System.Type> type_dc = null)
        {
            DataTable dt = new DataTable();
            string[] headers;

            using (FileStream fileStream = new FileStream(f_path, FileMode.Open, FileAccess.Read))
            using (StreamReader reader = new StreamReader(fileStream, Encoding.Default))
            {
                string headerLine = reader.ReadLine();
                headers = headerLine.Split(',');
                foreach (var col in headers)
                {
                    if (type_dc != null && type_dc.ContainsKey(col))
                        dt.Columns.Add(col, type_dc[col]);
                    else
                        dt.Columns.Add(col);
                }
            }

            // 读取UTF-8编码的CSV文件
            using (var reader = new StreamReader(f_path, Encoding.UTF8))
            using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture) { Encoding = Encoding.UTF8 }))
            {
                // 读取CSV文件中的所有记录
                var records = csv.GetRecords<dynamic>();
                foreach (var record in records)
                {
                    // 将记录转换为JSON字符串
                    var json = JsonConvert.SerializeObject(record);
                    var dc = BdUtils.conv_json_to_dict(json);

                    var dr = dt.NewRow();
                    for (var j = 0; j < headers.Length; j++)
                    {
                        var col = headers[j];
                        var value = dc[col];

                        dr[j] = value;
                    }
                    dt.Rows.Add(dr);
                }
            }

            if (primary_key != null)
                dt.PrimaryKey = new DataColumn[] { dt.Columns[primary_key] };
            return dt;
        }

        public static DataTable read_excel(string file_path, string primary_key = null, string table_name = null)
        {
            using (var package = new ExcelPackage(new FileInfo(file_path)))
            {
                var worksheet = package.Workbook.Worksheets[0];
                if (table_name == null)
                    table_name = file_path;
                var dt = new DataTable(table_name);
                var startRow = worksheet.Dimension.Start.Row;
                var endRow = worksheet.Dimension.End.Row;
                var startCol = worksheet.Dimension.Start.Column;
                var endCol = worksheet.Dimension.End.Column;
                for (int col_i = startCol; col_i <= endCol; col_i++)
                {

                    // if (type_dc != null && type_dc.ContainsKey(col))
                    //     dt.Columns.Add(col, type_dc[col]);
                    // else
                    //     dt.Columns.Add(col);

                    dt.Columns.Add(worksheet.Cells[startRow, col_i].Value.ToString());
                }
                for (int row = startRow + 1; row <= endRow; row++)
                {
                    var dr = dt.NewRow();
                    for (int col = startCol; col <= endCol; col++)
                    {
                        var cell = worksheet.Cells[row, col];
                        var value = cell.Value;

                        bool isDateTime = BdUtils.get_type(value) == "System.DateTime";
                        if (isDateTime)
                        {
                            value = ((DateTime)value).ToString("yyyy-MM-dd hh:mm:ss");
                            // BdUtils.log("isDateTime --- ", isDateTime, "---", header_i, value, BdUtils.get_type(value));
                        }

                        dr[col - startCol] = value;
                    }
                    dt.Rows.Add(dr);
                }

                if (primary_key != null)
                    dt.PrimaryKey = new DataColumn[] { dt.Columns[primary_key] };
                return dt;
            }
        }

        public static int CountChineseChars(string input)
        {
            int count = 0;
            Regex regex = new Regex("[\u4e00-\u9fa5]");
            MatchCollection matches = regex.Matches(input);
            count = matches.Count;
            return count;
        }

        public static DataTable sort_data_table(DataTable dt, string ordering)
        {
            dt.DefaultView.Sort = ordering;
            DataTable ret = dt.DefaultView.ToTable();
            return ret;
        }

        public static void show_data_table(DataTable dt, int zh_len = 5, string select = null)
        {

            int max_len = zh_len * 2;

            BdUtils.assert(max_len >= 6, "max_len最小为6!");
            BdUtils.assert(max_len % 2 == 0, "为了兼容中文字符, max_len必须为偶数!");

            string my_format_str = "{0,-" + max_len + "} ";
            string column_str = "";

            foreach (DataColumn column in dt.Columns)
            {
                var _name = column.ColumnName;

                column_str += string.Format(my_format_str, _name);

                int n_zh = TableUtils.CountChineseChars(_name);
                column_str = column_str.Substring(0, column_str.Length - n_zh);

                // column_str += "|";
            }
            BdUtils.log(column_str);


            string row_str = "";


            // var rows = dt.Rows;

            List<object> rows;
            if (select != null)
                rows = BdUtils.conv_obj_to_list(dt.Select(select));
            else
                rows = BdUtils.conv_obj_to_list(dt.Rows);


            foreach (DataRow row in rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string _r = row[i].ToString();

                    int n_zh = CountChineseChars(_r);

                    if (n_zh > 0 && _r.Length + n_zh > max_len)
                    {
                        int _i = 0;
                        int _len = 0;
                        while (_len <= max_len - 2)
                        {
                            var _s = _r.Substring(_i, 1);
                            if (CountChineseChars(_s) == 1)
                                _len += 2;
                            else
                                _len += 1;
                            _i += 1;
                        }

                        string _end;

                        try
                        {
                            _end = String.Concat(System.Linq.Enumerable.Repeat(" ", n_zh));
                        }
                        catch (Exception e)
                        {
                            BdUtils.log("*** n_zh: ", n_zh, " --- _r:", _r);
                            throw new Exception($"*** error: {e}");
                        }

                        BdUtils.assert(_i > 0, "`max_len`设置的太短了会报错的...");
                        _r = _r.Substring(0, _i - 1) + ".." + _end;
                    }

                    else if (_r.Length > max_len)
                    {
                        _r = _r.Substring(0, max_len - 2) + "..";
                    }

                    row_str += string.Format(my_format_str, _r);
                    row_str = row_str.Substring(0, row_str.Length - n_zh);

                    // row_str += "|";
                }

                row_str += "\n";
            }

            BdUtils.log(row_str);
        }

        public static void show_one_row(DataRow row1)
        {
            DataTable dt = row1.Table;
            foreach (DataColumn column in dt.Columns)
            {
                var _name = column.ColumnName;
                BdUtils.log(_name, row1[_name]);
            }
        }

        // --- 指定列名, 该列将转换为list
        public static List<T> conv_one_col_to_ls<T>(DataRow[] rows, string col_name)
        {


            // List<T> ret = dataTable.AsEnumerable()
            //                    .Select(row => row.Field<T>(columnName))
            //                    .ToList();
            // string col_name = "序号";
            var ret = new List<T>();

            foreach (var row_i in rows)
                // ret.Add((T)row_i[col_name]);
                ret.Add((T)Convert.ChangeType(row_i[col_name], typeof(T)));
            return ret;
        }

        public static List<Dictionary<string, T>> conv_rows_to_dc_ls<T>(DataRow[] rows)
        {
            var ret = new List<Dictionary<string, T>>();

            foreach (var row_i in rows)
                ret.Add(conv_row_to_dc<T>(row_i));
            return ret;
        }

        public static Dictionary<string, T> conv_row_to_dc<T>(DataRow row1)
        {

            Dictionary<string, T> dc = new Dictionary<string, T>();

            DataTable dt = row1.Table;

            foreach (DataColumn column in dt.Columns)
            {
                string key = column.ColumnName;
                // T value = (T)row1[key];
                T value = (T)Convert.ChangeType(row1[key], typeof(T));

                dc.Add(key, value);
            }
            return dc;
        }

        public static Dictionary<string, object> conv_row_to_dc(DataRow row1)
        {

            var dc = conv_row_to_dc<object>(row1);
            return dc;
        }

        public static DataRow get(DataTable dt, object pk = null, int? index = null, string select = null, bool show_data = false)
        {
            DataRow row1 = null;


            if (index != null)
                row1 = dt.Rows[(int)index];
            else if (pk != null)
            {
                BdUtils.assert(dt.PrimaryKey.Length, $"*** datatable[{dt.TableName}]没有主键!");
                row1 = dt.Rows[0];

            }
            else if (select != null)
                row1 = dt.Select(select)[0];
            else
                BdUtils.assert(0, "index, pk和select不能同时为空!");

            if (show_data)
            {
                show_one_row(row1);
            }
            return row1;
        }

        public static void remove_rows(DataTable dt, DataRow[] rows)
        {
            foreach (var row in rows)
            {
                dt.Rows.Remove(row);
            }
        }

        // --- 获取子表
        public static DataTable new_sub_table(DataTable dt, DataRow[] rows = null, string[] cols = null)
        {
            DataTable dt2;
            if (cols != null && cols.Length > 0)
                dt2 = dt.DefaultView.ToTable(false, cols);
            else
                dt2 = dt.Clone();

            if (rows != null && rows.Length > 0)
            {
                foreach (DataRow row in rows)
                {
                    dt2.ImportRow(row);
                }
            }
            return dt2;
        }

        // public static DataTable swap_row(DataTable dt, int[] row_indexes)
        // {
        //     return dt;
        // }

        public static DataTable swap_row(DataTable dt, int row1Index, int row2Index)
        {
            // --- 交换table中两行的位置
            if (row1Index < 0 || row1Index >= dt.Rows.Count ||
                row2Index < 0 || row2Index >= dt.Rows.Count)
            {
                throw new ArgumentException("The row indexes must be within the bounds of the DataTable.");
            }

            DataRow dr = dt.NewRow();

            dr.ItemArray = dt.Rows[row1Index].ItemArray;

            DataColumn[] primaryKeyColumns = dt.PrimaryKey;
            dt.PrimaryKey = null;

            dt.Rows[row1Index].ItemArray = dt.Rows[row2Index].ItemArray;
            dt.Rows[row2Index].ItemArray = dr.ItemArray;

            dt.PrimaryKey = primaryKeyColumns;
            return dt;


            // // DataRow row1 = dt.Rows[row1Index];
            // DataRow row1 = dt.Rows[row1Index];
            // DataRow row2 = dt.Rows[row2Index];

            // for (int i = 0; i < dt.Columns.Count; i++)
            // {
            //     object temp = row1[i];
            //     row1[i] = row2[i];
            //     row2[i] = temp;
            // }
        }


        public static void test1()
        {

        }

    }


    public class MyClient
    {
        public static void run()
        {
            // --- 读取csv
            // string f_path = @"data/上传路径选项.csv";
            // TableUtils.show_csv_data(f_path);
            // var dt = TableUtils.read_csv(f_path, primary_key: "ID");

            // --- 指定列的类型读取
            // Dictionary<string, System.Type> type_dc = new Dictionary<string, System.Type>();
            // type_dc.Add("ID", typeof(int));
            // var dt = TableUtils.read_csv(f_path, primary_key: "ID", type_dc: type_dc);
            // var dt = TableUtils.read_csv(f_path, primary_key: "ID");
            // dt = TableUtils.sort_data_table(dt, "age desc");
            // dt = TableUtils.sort_data_table(dt, "age asc");
            // TableUtils.show_data_table(dt, zh_len: 15);

            // --- 类型转换
            // DataTable dt = _dt.Clone();
            // // BdUtils.log("------ ", BdUtils.get_type(typeof(Int32)));
            // dt.Columns["ID"].DataType = typeof(Int32);
            // foreach (DataRow row in _dt.Rows)
            // {
            //     dt.ImportRow(row);
            // }

            // dt.Columns["ID"].DataType = typeof(int);
            // foreach (DataRow row in dtCloned.Rows)
            // {
            //     // row["ID"] = Convert.ToInt32(row["ID"]);

            //     // var row = TableUtils.conv_row_to_dc(_row);
            //     // row["ID"] = Convert.ToInt32(row["ID"]);

            //     BdUtils.log(BdUtils.get_type(row["ID"]));
            //     // BdUtils.log("--- rows len:", rows.Length, BdUtils.get_type(rows[0]["ID"]));
            // }

            // --- 读取excel
            string f_path = @"data/红色动态表.xls";
            // DataTable dt = TableUtils.read_excel(f_path, primary_key: "序号");
            // DataTable dt = TableUtils.read_excel(f_path, table_name: "haha");  // 没有主键, 不推荐
            DataTable dt = TableUtils.read_excel(f_path);  // 没有主键, 不推荐

            // dt = TableUtils.swap_row(dt, 0, 3);
            // var row = TableUtils.get(dt, 4);

            // var row = TableUtils.get(dt, index: 3);
            // row["作者"] = "张三";

            // var r_dc = TableUtils.conv_row_to_dc(row);
            // BdUtils.log(" --- row:", r_dc);

            // --- 列操作
            List<string> l2 = BdUtils.conv_obj_to_list<string>(dt.Columns);
            // var col_ls = l2.Take(3).ToArray();
            var col_ls = l2.GetRange(0, 1).ToArray();
            // var col_ls = l2.GetRange(2, 3).ToArray();

            BdUtils.log(" === col_ls:", col_ls);
            // List<string> l2 = l1.ConvertAll<string>(x => x.ToString());

            DataTable dt2 = TableUtils.new_sub_table(dt, cols: col_ls);

            // DataTable dt2 = dt.DefaultView.ToTable(false, col_ls);

            // BdUtils.log(" === dt2.col_name:", dt2.Columns[0]);
            // foreach (var row in dt2.Select())
            //     BdUtils.log(" ~~~", TableUtils.conv_row_to_dc<int>(row));

            // BdUtils.log("--- dc_ls:", TableUtils.conv_rows_to_dc_ls<string>(dt2.Select()));
            // BdUtils.log("--- dc_ls:", TableUtils.conv_one_col_to_ls<int>(dt2.Select(), dt2.Columns[0].ToString()));
            BdUtils.log("--- dc_ls:", TableUtils.conv_one_col_to_ls<string>(dt.Select(), "类型"));
            BdUtils.log("--- dc_ls:", BdUtils.conv_obj_to_list<string>(new string[] { "a", "1", "b" }));
            // DataTableColumnToList(dt,

            // BdUtils.log(" === col_ls:", BdUtils.conv_obj_to_list(col_ls));

            // DataTable dt2 = TableUtils.new_sub_table(dt, dt.Select());

            // var rows = dt.Select("类型 = '参观须知'");
            // dt.Rows.Remove(row);
            // dt.Rows.RemoveAt(0);
            // TableUtils.remove_rows(dt, rows);

            // TableUtils.show_data_table(dt2);

            // --- 行和列的展示
            // BdUtils.log(" --- col:", dt.Columns.Count, BdUtils.conv_obj_to_list(dt.Columns));
            // var r_dc = TableUtils.conv_row_to_dc(row);
            // BdUtils.log(" --- row:", dt.Rows.Count, r_dc);

            //  --- 交换两行
            // int row1Index = 1;
            // int row2Index = 2;

            // DataRow row1 = dt.Rows[row1Index];
            // DataRow row2 = dt.Rows[row2Index];

            // object temp = row1;


            // --- 子表
            // DataTable dt2 = TableUtils.new_sub_table(dt, dt.Select("类型 = '参观须知'", "序号 desc"));
            // DataTable dt2 = TableUtils.new_sub_table(dt, dt.Select("类型 = '参观须知'"));
            // TableUtils.show_data_table(dt2, zh_len: 10);

            // --- 排序
            // dt = TableUtils.sort_data_table(dt, "序号 desc");
            // dt = TableUtils.sort_data_table(dt, "发布时间 desc");
            TableUtils.show_data_table(dt, zh_len: 10);

            // --- get 单一查找
            // DataRow row1 = dt.Rows.Find("2");
            // DataRow row1 = TableUtils.get(dt, 3);

            // --- select 批量查找
            // var rs = dt.Select("ID <= 2");
            // BdUtils.log(rs[1]);
            // BdUtils.ReflectionUtils.show_properties(rs);

            // DataRow row1 = TableUtils.get(dt, select: "ID=1");
            // DataRow row1 = TableUtils.get(dt, pk: 1);
            // var row1 = rs[0];

            // --- 展示一行
            // TableUtils.show_one_row(row1);


            // --- 类似`pandas`的`df.apply`
            // var rows = dt.Select("ID <= 2");
            // // var r = TableUtils.get(dt, 2);  // , r["ID"]
            // BdUtils.log("--- rows len:", rows.Length, BdUtils.get_type(rows[0]["ID"]));
            // // Define a delegate to perform the operation on each row
            // Func<DataRow, int> rowSum = delegate (DataRow row)
            // {
            //     int sum = 0;
            //     var dc = TableUtils.conv_row_to_dc(row);
            //     sum += (int)BdUtils.conv_to_int(dc["印刷页码"], 0);
            //     sum += (int)BdUtils.conv_to_int(dc["物理页码"], 0);
            //     return sum;
            // };

            // foreach (DataRow row in rows)
            // {
            //     var dc = TableUtils.conv_row_to_dc(row);

            //     int sum = rowSum(row);
            //     BdUtils.log("--- ID: ", dc["ID"], "Row sum: " + sum, "--- 印刷页码:", dc["印刷页码"], "--- 物理页码:", dc["物理页码"]);
            // }

            // var dc = TableUtils.conv_row_to_dc(row1);

            // // // string colname = "序号";
            // // // string colname = "作者";
            // string colname = "发布时间";
            // var res = BdUtils.conv_to_int(dc[colname]);
            // BdUtils.log("\n --- ", colname, dc[colname], res, res == null, BdUtils.is_null(res), BdUtils.get_type(res));
        }

        public static void test_datatable()
        {

            // 创建 DataTable
            DataTable dt = new DataTable();

            // 添加列
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));
            dt.Columns.Add("Gender", typeof(string));

            dt.PrimaryKey = new DataColumn[] { dt.Columns["Name"] };

            // 添加行
            dt.Rows.Add("John", 25, "Male");
            dt.Rows.Add("Mike", 32, "Male");
            dt.Rows.Add("Emily", 29, "Female");

            // 遍历 DataTable
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("Name: " + row["Name"]);
                Console.WriteLine("Age: " + row["Age"]);
                Console.WriteLine("Gender: " + row["Gender"]);
            }

            BdUtils.log("************* Find ***");
            DataRow row1 = dt.Rows.Find("Mike");
            // DataRow row1 = dt.Rows.Find(29);

            Console.WriteLine("Name: " + row1["Name"]);
            Console.WriteLine("Age: " + row1["Age"]);
            Console.WriteLine("Gender: " + row1["Gender"]);

            BdUtils.log("************* Select ***");
            DataRow[] result = dt.Select("Age = 32 or Gender = 'Female'");
            foreach (DataRow row in result)
            {
                Console.WriteLine("Name: " + row["Name"]);
                Console.WriteLine("Age: " + row["Age"]);
                Console.WriteLine("Gender: " + row["Gender"]);
                BdUtils.log(BdUtils.end_string);
            }
        }

    }
}
